package com.example.gmallpublisher.mapper;

import com.example.gmallpublisher.bean.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author : songyuan
 * @Description :
 * Date : 2022/8/22 14:13
 * @Version : 1.0
 */
public interface TrafficScStatsMapper {
    // 1. 获取各渠道独立访客数
    @Select("select sc,\n" +
            "       sum(uv_ct)           uvct\n" +
            "from dws_traffic_sc_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt), sc\n" +
            "order by uvct desc;")
    List<TrafficUvCt> selectUvCt(@Param("date")Integer date);

    // 2. 获取各渠道会话数
    @Select("select sc,\n" +
            "       sum(sv_ct)           svct\n" +
            "from dws_traffic_sc_new_page_view_window\n" +
            "where cast(toYYYYMMDD(stt) as  UInt32) = #{date}\n" +
            "group by toYYYYMMDD(stt), sc\n" +
            "order by svct desc;")
    List<TrafficSvCt> selectSvCt(@Param("date")Integer date);

    // 3. 获取各渠道会话平均页面浏览数
    @Select("select sc,\n" +
            "       sum(pv_ct) / sum(sv_ct)   pvPerSession\n" +
            "from dws_traffic_sc_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt), sc\n" +
            "order by pvPerSession desc;")
    List<TrafficPvPerSession> selectPvPerSession(@Param("date")Integer date);

    // 4. 获取各渠道会话平均页面访问时长
    @Select("select sc,\n" +
            "       sum(dur_sum) / sum(sv_ct) durPerSession\n" +
            "from dws_traffic_sc_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt), sc\n" +
            "order by durPerSession desc;")
    List<TrafficDurPerSession> selectDurPerSession(@Param("date")Integer date);

    // 5. 获取各渠道跳出率
    @Select("select sc,\n" +
            "       sum(uj_ct) / sum(sv_ct)   ujRate\n" +
            "from dws_traffic_sc_new_page_view_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by toYYYYMMDD(stt), sc\n" +
            "order by ujRate desc;")
    List<TrafficUjRate> selectUjRate(@Param("date")Integer date);
}
